--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

/*DROP TABLE se_pcp_repent;
CREATE TABLE se_pcp_repent AS (SELECT e.* FROM &erp.pcp_repent e WHERE 1=2);
DROP TABLE se_pcp_repdet;
CREATE TABLE se_pcp_repdet AS (SELECT d.* FROM &erp.pcp_repdet d WHERE 1=2);  */

DELETE FROM se_pcp_repent;
DELETE FROM se_pcp_repdet;





/*SELECT e.rep_cod,e.PERIODE_VAL,e.JOUR_DAT,e.COMBSTO_ID, d.*
FROM   &erp.pcp_repent e, &erp.pcp_repdet d
WHERE e.rep_cod='AN_ART_MO'
  AND d.parentenreg_id(+)=e.enreg_id */



 INSERT INTO se_pcp_repent
 (SELECT distinct
      'ART_MO' rep_cod,
      to_date('01/01/'||c.aaaa,'dd/mm/yyyy') deb_dat,
      NULL fin_dat,
      null periode_val,
      null jour_dat,
      Decode(p.codgestion,'5',conv.combsto_ref,p.numspecif) combsto_id,
      NULL unfab_cod,
      NULL plan_cod,
      NULL cpte_cod,
      0 enreg_id,
      NULL libre_val
  FROM UV_PP_PREVENTESA c, uv_pproduits p, temp_conv_rgp_combstoref conv
  WHERE c.numspecif=p.id(+)
    AND c.client='PICDM'
    AND conv.clergp(+)=p.id ) ;

 DELETE FROM se_pcp_repent WHERE rep_cod='ART_MO' AND COMBSTO_ID IS NULL ;
 DELETE FROM se_pcp_repent r WHERE NOT EXISTS (SELECT 1 FROM &erp.art_comp c WHERE c.combsto_id= r.combsto_id)  ;

 UPDATE se_pcp_repent tf SET enreg_id=Nvl((select Max(enreg_id) FROM &erp.pcp_repent f WHERE f.rep_cod=tf.rep_cod AND f.combsto_id =tf.combsto_id AND f.DEB_DAT=tf.DEB_DAT),0) ; ;
 UPDATE se_pcp_repent tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id=0 ;

 --DELETE &erp.pcp_repent r WHERE EXISTS (SELECT 1 from se_pcp_repent rr WHERE rr.enreg_id=r.enreg_id)
 COMMIT ;


 INSERT INTO se_pcp_repdet
 (SELECT
      Nvl((SELECT enreg_id FROM se_pcp_repent e
       WHERE e.rep_cod='ART_MO'
         AND e.combsto_id=Decode(p.codgestion,'5',conv.combsto_ref,p.numspecif)
         AND e.deb_dat=to_date('01/01/'||c.aaaa,'dd/mm/yyyy')
         ),0)   parentenreg_id,
      null periode_val,
      null jour_dat,
      NULL combsto_id,
      null unfab_cod,
      NULL cpte_cod,
      NULL plan_cod,
      0 enreg_id,
      NULL repp_val,
      repartmois repc_val,
      LPad(cr.RANGMULTI2+1,2,'0') libre_val
  FROM UV_PP_PREVENTESA c, uv_pp_preventesa_repartmois cr, uv_pproduits p, (SELECT DISTINCT clergp,COMBSTO_REF FROM temp_conv_rgp_combstoref) conv
  WHERE c.numspecif=p.id(+)
    AND cr.id=c.id
    AND c.client='PICDM'
    AND conv.clergp(+)=p.id ) ;

 DELETE FROM se_pcp_repdet WHERE parentenreg_id=0 ;
 --DELETE &erp.pcp_repdet r WHERE EXISTS (SELECT 1 from se_pcp_repent rr WHERE rr.enreg_id=r.parentenreg_id AND rr.rep_cod='AN_ART_MO');
 UPDATE se_pcp_repdet tf SET enreg_id=Nvl((select enreg_id FROM &erp.pcp_repdet f WHERE f.parentenreg_id=tf.parentenreg_id AND f.libre_val=tf.libre_val),0) ;
 UPDATE se_pcp_repdet tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id=0 ;

 DELETE FROM &erp.pcp_repdet gg WHERE EXISTS (SELECT 1 FROM &erp.pcp_repent g WHERE rep_cod='ART_MO' AND gg.parentenreg_id=g.enreg_id) ;
 DELETE FROM &erp.pcp_repent gg WHERE rep_cod='ART_MO' ;


CALL uvse_synchroverserp('rep_cod@deb_dat@combsto_id','PCP_REPENT','O') ;
CALL uvse_synchroverserp('parentenreg_id@libre_val','PCP_REPDET','O') ;




 commit;


select * from (
SELECT &outils.stragg(source) source,rep_cod, deb_dat, fin_dat, periode_val, jour_dat, combsto_id, unfab_cod, plan_cod, cpte_cod, enreg_id, libre_val
FROM (
  SELECT 'uv' source,e.* FROM   se_pcp_repent e
  WHERE e.rep_cod='ART_MO'

  UNION ALL
  SELECT 'se' source,e.* FROM   &erp.pcp_repent e
  WHERE e.rep_cod='ART_MO'
  )
GROUP BY rep_cod, deb_dat, fin_dat, periode_val, jour_dat, combsto_id, unfab_cod, plan_cod, cpte_cod, enreg_id, libre_val
HAVING Count(1)!=2) r
ORDER BY combsto_id ;